﻿CREATE VIEW dbo.v_PurchaseContract
AS
SELECT     dbo.v_PurchaseContract_40.LoanID, LU.[Customer #] AS UsixID, LU.CompanyID AS U, dbo.v_PurchaseContract_40.ContractHolder, 
                      dbo.v_PurchaseContract_40.ContractHolderID, dbo.v_PurchaseContract_40.DownPayment, dbo.v_PurchaseContract_40.DownPayment_Min, 
                      dbo.v_PurchaseContract_40.DownPaymentRatio, dbo.v_PurchaseContract_40.DownPaymentOK, dbo.v_PurchaseContract_40.Lamp_DP, 
                      dbo.v_PurchaseContract_40.AmountFinanced, dbo.v_PurchaseContract_40.AF_Adjusted, dbo.v_PurchaseContract_40.AFRatio_Max, 
                      dbo.v_PurchaseContract_40.AF_MaxByBookValue, dbo.v_PurchaseContract_40.SalesPrice, dbo.v_PurchaseContract_40.SellPriceRatio_Max, 
                      dbo.v_PurchaseContract_40.SellPrice_MaxByBookValue, dbo.v_PurchaseContract_40.LoanTerm, dbo.v_PurchaseContract_40.Term_Max, 
                      dbo.v_PurchaseContract_40.Lamp_Term, dbo.v_PurchaseContract_40.APR, dbo.v_PurchaseContract_40.APR_Min, 
                      dbo.v_PurchaseContract_40.Lamp_APR, dbo.v_PurchaseContract_40.Payment, dbo.v_PurchaseContract_40.PaymentPerMonth, 
                      dbo.v_PurchaseContract_40.NumberOfPayments, dbo.v_PurchaseContract_40.TotalPayments, dbo.v_PurchaseContract_40.TotalPayments_Min, 
                      dbo.v_PurchaseContract_40.APRFee, dbo.v_PurchaseContract_40.ModelAge, dbo.v_PurchaseContract_40.BookValue, 
                      dbo.v_PurchaseContract_40.BookValueDesc, dbo.v_PurchaseContract_40.MilesPerYear, dbo.v_PurchaseContract_40.MileagePerYear_Max, 
                      dbo.v_PurchaseContract_40.Lamp_Miles, dbo.v_PurchaseContract_40.Miles_Max, dbo.v_PurchaseContract_40.Overage, 
                      dbo.v_PurchaseContract_40.AF_Max, dbo.v_PurchaseContract_40.Overage_AF_Max, dbo.v_PurchaseContract_40.Overage_AF, 
                      dbo.v_PurchaseContract_40.Overage_SP, dbo.v_PurchaseContract_40.OverageFee, dbo.v_PurchaseContract_40.Fee, 
                      dbo.v_PurchaseContract_40.Advance, dbo.v_PurchaseContract_40.OverageFeeRatio, dbo.v_PurchaseContract_40.OverageAllowed, 
                      dbo.v_PurchaseContract_40.FeeRate, dbo.v_PurchaseContract_40.Fee_Max, dbo.v_PurchaseContract_40.PTI_Max, dbo.v_PurchaseContract_40.PTI, 
                      dbo.v_PurchaseContract_40.DTI_Max, dbo.v_PurchaseContract_40.DTI, dbo.v_PurchaseContract_40.OtherFeeName, 
                      dbo.v_PurchaseContract_40.OtherFeeValue, dbo.v_PurchaseContract_40.TotalDeductFee, dbo.v_PurchaseContract_40.RecourseMonths, 
                      dbo.v_PurchaseContract_40.AddMosRecourse, dbo.v_PurchaseContract_40.DeductFeeFromReserve, CONVERT(numeric(9, 2), 
                      dbo.v_PurchaseContract_40.AmountFinanced * dbo.v_PurchaseContract_40.Advance / 100 - CASE WHEN DeductFeeFromReserve < 0 THEN 0 ELSE TotalDeductFee
                       END - dbo.v_PurchaseContract_40.Fee + dbo.v_PurchaseContract_40.TrackerReward) AS CheckToDealer, CONVERT(numeric(9, 2), 
                      dbo.v_PurchaseContract_40.AmountFinanced * (100 - dbo.v_PurchaseContract_40.Advance) 
                      / 100 + CASE WHEN DeductFeeFromReserve >= 0 THEN TotalDeductFee ELSE 0 END) AS Reserve, dbo.v_PurchaseContract_40.GrossIncome, 
                      dbo.v_PurchaseContract_40.NonEmploymentIncome, dbo.v_PurchaseContract_40.DebtNoCar, dbo.v_PurchaseContract_40.HousePayment, 
                      dbo.v_PurchaseContract_40.Installment, dbo.v_PurchaseContract_40.Revolving, dbo.v_PurchaseContract_40.JobTime_Min, 
                      dbo.v_PurchaseContract_40.JobTimeCurrent, dbo.v_PurchaseContract_40.ResidenceTime_Min, dbo.v_PurchaseContract_40.AreaTime_Min, 
                      dbo.v_PurchaseContract_40.MonthsCurrent, dbo.v_PurchaseContract_40.MonthsInArea, dbo.v_PurchaseContract_40.CurrPrevDistance, 
                      dbo.v_PurchaseContract_40.ModelAge_Max, dbo.v_PurchaseContract_40.LoanTermOK, dbo.v_PurchaseContract_40.APR_OK, 
                      dbo.v_PurchaseContract_40.MilesPerYearOK, dbo.v_PurchaseContract_40.DTI_OK, dbo.v_PurchaseContract_40.PTI_OK, 
                      dbo.v_PurchaseContract_40.ModelAgeOK, dbo.v_PurchaseContract_40.Lamp_DTI, dbo.v_PurchaseContract_40.Lamp_PTI, 
                      dbo.v_PurchaseContract_40.Lamp_ModelAge, dbo.v_PurchaseContract_40.Lamp_ResTime, dbo.v_PurchaseContract_40.Lamp_JobTime, 
                      dbo.v_PurchaseContract_40.ResidenceTimeOK, dbo.v_PurchaseContract_40.JobTimeOK, dbo.v_PurchaseContract_40.OpenUnitsAdjusted, 
                      dbo.v_PurchaseContract_40.OpenUnitsOK, dbo.v_PurchaseContract_40.Lamp_OpenUnits, dbo.v_PurchaseContract_40.StatusID, 
                      dbo.t_State.Name AS Status, dbo.v_PurchaseContract_40.OK, dbo.ShowFlag_Lamp(dbo.v_PurchaseContract_40.OK) AS Lamp_OK, 
                      dbo.v_PurchaseContract_40.ContactName, dbo.v_PurchaseContract_40.ContactPhone, dbo.v_PurchaseContract_40.ContactEmail, 
                      dbo.v_PurchaseContract_40.ContactFax, dbo.v_PurchaseContract_40.ContractHolderStreet, dbo.v_PurchaseContract_40.ContractHolderCity, 
                      dbo.v_PurchaseContract_40.ContractHolderState, dbo.v_PurchaseContract_40.ContractHolderZip, 
                      dbo.v_PurchaseContract_40.ContractHolderStateName, dbo.v_PurchaseContract_40.AgreementDay, dbo.v_PurchaseContract_40.TrackerReward, 
                      dbo.v_PurchaseContract_40.ServiceContract_Max, dbo.v_PurchaseContract_40.ServiceContract, dbo.v_PurchaseContract_40.ServiceContractOK, 
                      dbo.v_PurchaseContract_40.Lamp_ServiceContract, dbo.v_PurchaseContract_40.FirstName, dbo.v_PurchaseContract_40.LastName, 
                      dbo.v_PurchaseContract_40.PurchaseBatchID, dbo.v_PurchaseContract_40.IsSUV, dbo.v_PurchaseContract_40.TrackerTypeID, 
                      dbo.v_PurchaseContract_40.DealerID, dbo.v_PurchaseContract_40.Mileage, dbo.v_PurchaseContract_40.GAP, 
                      dbo.v_PurchaseContract_40.IsIndependent, dbo.v_PurchaseContract_40.AddOnRateByPayment, dbo.v_CarInfo_10.Make, dbo.v_CarInfo_10.CarTypeID, 
                      dbo.v_PurchaseContract_40.PLTV, dbo.v_PurchaseContract_40.PaymentsPerYear, 
                      CEILING(dbo.PaymentFreqWeight(dbo.v_PurchaseContract_40.PaymentsPerYear) * dbo.v_PurchaseContract_40.LoanTerm) AS LoanTermInMonth, 
                      dbo.v_PurchaseContract_40.ToDealerFromPayments, 2500 - ISNULL(dbo.v_PurchaseContract_40.DownPayment, 0) 
                      - ISNULL(dbo.v_PurchaseContract_40.ToDealerFromPayments, 0) AS ToDealer, dbo.v_GL_LoanProgram.Name AS GLProgramName, 
                      dbo.t_Loan.GLProgramID
FROM         dbo.t_Loan INNER JOIN
                      dbo.v_PurchaseContract_40 INNER JOIN
                      dbo.t_State ON dbo.v_PurchaseContract_40.StatusID = dbo.t_State.ID INNER JOIN
                      dbo.v_CarInfo_10 ON dbo.v_PurchaseContract_40.LoanID = dbo.v_CarInfo_10.LoanID ON 
                      dbo.t_Loan.ID = dbo.v_PurchaseContract_40.LoanID INNER JOIN
                      dbo.v_GL_LoanProgram ON dbo.t_Loan.GLProgramID = dbo.v_GL_LoanProgram.ID LEFT OUTER JOIN
                      usix.t_CustomerFile_Export AS LU ON dbo.v_PurchaseContract_40.LoanID = LU.LoanID

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[27] 4[51] 2[2] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1[50] 4[25] 3) )"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[30] 2[40] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[47] 4[40] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1[38] 4) )"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4[60] 2) )"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4) )"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 1
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "t_Loan"
            Begin Extent = 
               Top = 285
               Left = 677
               Bottom = 532
               Right = 857
            End
            DisplayFlags = 280
            TopColumn = 9
         End
         Begin Table = "v_PurchaseContract_40"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 575
               Right = 244
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_State"
            Begin Extent = 
               Top = 358
               Left = 350
               Bottom = 494
               Right = 501
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "v_CarInfo_10"
            Begin Extent = 
               Top = 30
               Left = 521
               Bottom = 220
               Right = 724
            End
            DisplayFlags = 280
            TopColumn = 32
         End
         Begin Table = "v_GL_LoanProgram"
            Begin Extent = 
               Top = 216
               Left = 895
               Bottom = 294
               Right = 1046
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "LU"
            Begin Extent = 
               Top = 69
               Left = 287
               Bottom = 177
               Right = 476
            End
            DisplayFlags = 280
            TopColumn = 12
         End
      End
   End
   Begin SQLPane = 
      PaneHidden = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 46
         Width = 284', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_PurchaseContract';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'Width = 1080
         Width = 960
         Width = 1290
         Width = 900
         Width = 705
         Width = 990
         Width = 885
         Width = 975
         Width = 540
         Width = 795
         Width = 930
         Width = 990
         Width = 1155
         Width = 1020
         Width = 1275
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 10155
         Alias = 2385
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_PurchaseContract';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_PurchaseContract';

